<?php
  /**
   * This is the DB class for the framework
   *
   * @author Macdonald Terrence Robinson
   * @package Core
   *
   */
  final class DB
  {
    /** @var PDO */
    private $dbHandler = NULL;
    
    private $returnType = PDO::FETCH_CLASS;
    private $className = 'stdClass';
    
    public function __construct($dbType, $odbcDriver, $dbHost, $dbName, $dbUser, $dbPassword, $connectionString)
    {      
      try
      {        
        switch( strtolower($dbType) )
        {
          case 'mysql':
            $this->dbHandler = new PDO("$dbType:host=$dbHost;dbname=$dbName", $dbUser, $dbPassword,array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'utf8';"));
          break;
          default:
            $this->dbHandler = new PDO("$dbType:$connectionString", $dbUser, $dbPassword);            
          break;
        }        
        $this->dbHandler->setAttribute( PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION );
      }
      catch(PDOException $e)
      {        
         $errorMessage = 'Error Connecting to DB - <br />';
         $errorMessage .= $e->getMessage();
         ErrorHandler::runTimeError($errorMessage);
      }
    }

    public function setFetchMode($mode)
    {
      $this->returnType = $mode;      
    }

    public function setClassName($className)
    {
      $this->className = $className;
    }

    /**
     * This function returns the raw PDO object
     * @return PDO
     */
    public function getDBHandler()
    {
      return $this->dbHandler;
    }
    
    public function generateCSV($rows)
    {
      if(count($rows)==0)
        return '';
        
      $csv = '';
      $writeLable = true;
      $rowIndex = 0;
      
      foreach ($rows[0] as $field=>$fieldValue)
      {
        if(is_numeric($field))
          continue;

        $csv .= $field.',';
      }

       $csv .="\n";
       $rowIndex++;
      
      foreach ($rows as $key=>$value)
      {
        foreach ($value as $field=>$fieldValue)
        {
          if(is_numeric($field))
            continue;
            
          $csv .= $fieldValue.',';
        }
        
       $csv .="\n";
       $rowIndex++;
      }
      
      return $csv;
    }
    
    public function loadCSVIntoTable( $pathToFile, $tableName, $delimiter=',' )
    {
      $sql = "
        LOAD DATA INFILE '$pathToFile'
        INTO TABLE `$tableName`
        FIELDS TERMINATED BY ','
      ";
      
      return $this->execute($sql);
    }
    
    public function execute($sql, $prepairArray = array())
    {      
      /** @var PDOStatement */
      $pdoStatement = NULL;
      
      try
      {
        if($this->dbHandler == NULL)
          die();
        //echo $sql;
        $pdoStatement = $this->dbHandler->prepare($sql);
        $result = $pdoStatement->execute($prepairArray);
        $errorInfo = $pdoStatement->errorInfo();        
      }
      catch(Exception $e)
      {        
        $errorMessage = 'Error executing SQL statement - <br />';
        $errorMessage .= $e->getMessage();
        
        ErrorHandler::runTimeError($errorMessage);
      }      
      
      return $pdoStatement;
    }

    public function generateKeyValuePair(array $keyValuePair, $fieldValueSeperator='=', $seperator=',', $quoteValues = true )
    {
      $keyValueString = '';
      $index = 0;
      $arraySize = count($keyValuePair);
      foreach ($keyValuePair as $key=>$value)
      {
        if($quoteValues)
          $keyValueString .= " $key$fieldValueSeperator'$value'";
        else
          $keyValueString .= " $key$fieldValueSeperator$value";
        

        if($index < $arraySize-1)
          $keyValueString.=' '.$seperator;

        $index++;
      }
      
      return $keyValueString;
    }
    
    
    public function generatePair(array $valuePair, $seperator=',', $encloseWith = '')
    {
      $valueString = '';
      $index = 0;
      $arraySize = count($valuePair);
      foreach ($valuePair as $key=>$value)
      {
        $valueString .= " $encloseWith$value$encloseWith ";

        if($index < $arraySize-1)
          $valueString.=' '.$seperator;

        $index++;
      }

      return $valueString;
    }
    
    public function createTable( $tableName, array $fieldNameTypePair )
    {
      $fieldValueString = $this->generateKeyValuePair( $fieldNameTypePair, ' ', ',', false );
      $sql = 'CREATE TABLE '.$tableName .' ('.$fieldValueString.' );';
      return $this->execute($sql);
    }
    
    public function dropTable( $tableName )
    {
      $sql = 'DROP TABLE '.$tableName.';';
      return $this->execute($sql);
    }
    
    public function select($tableName, array $where = array(), array $fields = array('*'),$orderStr = '')
    {      
      $fieldsString = $this->generatePair($fields, ',');
      $whereString = $this->generateKeyValuePair($where, '=', 'AND');

      $sql = 'SELECT '.$fieldsString.' FROM '.$tableName;

      if($whereString !='')
        $whereString = ' WHERE '.$whereString;      
      $sql = $sql.' '.$whereString;     
      if($orderStr!='')
				$sql = $sql.' '.$orderStr.';';  
      return $this->execute($sql)->fetchAll($this->returnType, $this->className);
    }

    public function selectSingle($tableName, array $where = array(), array $fields = array('*'))
    {
      $return = $this->select($tableName, $where, $fields);

      $single = NULL;

      if( count($return) > 0 )
        $single = $return[0];

      return $single;
    }
    
    public function insert($tableName, array $keyValuePair)
    {
      $fieldString = $this->generatePair(array_keys($keyValuePair),',');
      $valueString = $this->generatePair(array_values($keyValuePair),',','\'');

      $sql = "INSERT INTO $tableName ( $fieldString ) VALUES ( $valueString );";
      //print_r($sql);
      $this->execute($sql);
      
      return $this->dbHandler->lastInsertId();
    }
    
    public function update($tableName, array $keyValuePair, array $where)
    {
      $fieldValuePairString = $this->generateKeyValuePair($keyValuePair);
      $whereString = $this->generateKeyValuePair($where, '=', ',');
      
      if($whereString !='')
        $whereString = ' WHERE '.$whereString;      
      $sql = "UPDATE $tableName SET $fieldValuePairString $whereString;";
      return $this->execute($sql)->rowCount();
    }
    
    
    public function delete($tableName, array $where)
    {
      $whereString = $this->generateKeyValuePair($where, '=', ',');
      
      $whereString = ' WHERE '.$whereString;

      $sql = "DELETE FROM $tableName $whereString;";

      return $this->execute($sql)->rowCount();
    }
    
    public function optimizeTable($tableName)
    {
      $sql = 'OPTIMIZE TABLE '.$tableName;
      return $this->execute($sql);
    }
    
    public function listFields($tableName)       
    {
     
        //$stack = array();
        if(Config::$dbSettings['dbType']=="mysql")
        {
            $sql = ' DESCRIBE '.$tableName;	
            return $this->execute($sql)->fetchAll(PDO::FETCH_ASSOC);	  
        }else if(Config::$dbSettings['dbType'] == "sqlite")
        {
            $tablist  = $this->selectSingle('sqlite_master',array('name'=>$tableName));	    
            preg_match_all("(\[\w+)",$tablist['sql'], $matches);	   
            $stack = array();  
            for ($i=0; $i< count($matches[0]); $i++) {
            $Field = $matches[0][$i];
            $Field = str_replace("[","",$Field);		
            $Field = str_replace(")","",$Field);		
            //array_push($stack, array("Field"=>$Field));	
            if (strtolower($Field)=="id")
                array_push($stack, array("Field"=>$Field,"Key"=>"PRI"));
            else
                array_push($stack, array("Field"=>$Field));	
            } 
            return $stack;	    
        }	
    }
    
    public function listTables($database)
    {
        if(Config::$dbSettings['dbType']=="mysql")
        {
            $sql = ' SHOW TABLE STATUS from '.$database;
            return $this->execute($sql)->fetchAll(PDO::FETCH_ASSOC);	
        }else if(Config::$dbSettings['dbType'] == "sqlite")
        {
            $sql = "Select * From sqlite_master";
            $tablist = $this->execute($sql)->fetchAll(PDO::FETCH_ASSOC);	
            $stack = array();
            foreach($tablist as $key)
            {
                array_push($stack, array("Name"=>$key['name']));
            }
            return $stack;
        }	
    }        
    public function selectTatos($tableName, array $where = array())
    {      
      $whereString = $this->generateKeyValuePair($where, '=', 'AND');
      $sql = 'SELECT count(*) FROM '.$tableName;
      if($whereString !='')
        $whereString = ' WHERE '.$whereString;
      $sql = $sql.' '.$whereString.';';     
      //return $this->execute($sql)->fetchAll($this->returnType, $this->className);
      $totals = $this->execute($sql)->fetchAll();      
      if (count($totals)>0)
		return $totals[0][0];
	  else
		return 0;
    }    
    public function __destruct()
    {
      $this->dbHandler = NULL;
    }
    
  }
?>
